﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.SQLite;

/// <summary>
/// 数据库操作类，负责与SQLite数据库交互
/// </summary>
public class WordRepository
{
    private string connectionString = "Data Source=WordsDB.db;Version=3;";

    /// <summary>
    /// 获取所有单词
    /// </summary>
    public List<Word> GetAllWords()
    {
        List<Word> words = new List<Word>();
        try
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();

                // 检查表是否存在，不存在则创建
                using (SQLiteCommand cmd = new SQLiteCommand(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name='Words';", conn))
                {
                    var result = cmd.ExecuteScalar();
                    if (result == null)
                    {
                        using (SQLiteCommand createCmd = new SQLiteCommand(
                            "CREATE TABLE Words (Id INTEGER PRIMARY KEY AUTOINCREMENT, EnglishWord TEXT, ChineseMeaning TEXT);", conn))
                        {
                            createCmd.ExecuteNonQuery();
                        }
                    }
                }

                // 查询所有单词
                using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Words;", conn))
                {
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            words.Add(new Word
                            {
                                Id = reader.GetInt32(0),
                                EnglishWord = reader.GetString(1),
                                ChineseMeaning = reader.GetString(2)
                            });
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("数据库操作失败：" + ex.Message);
        }
        return words;
    }

    /// <summary>
    /// 添加新单词到数据库
    /// </summary>
    public void AddWord(string english, string chinese)
    {
        try
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Words (EnglishWord, ChineseMeaning) VALUES (@english, @chinese);";
                using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@english", english);
                    cmd.Parameters.AddWithValue("@chinese", chinese);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception("添加单词失败：" + ex.Message);
        }
    }
}
